package com.example.board.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.board.pojo.User;
import com.example.board.pojo.dto.DeptDto;
import com.example.board.pojo.dto.UserDto;
import com.example.board.pojo.vo.TopSaleStaticVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface PinCrownMapper extends BaseMapper<User> {

    @Select({
            " SELECT u.nickname userName , sd.province province,sd.city city,sd.district district ,u.id id ,u.avatar avatar ",
            " from sys_user u,sd_tool_man_info sd ",
            " WHERE u.id = sd.user_id and u.enabled = 1 ",
            " GROUP BY u.nickname,sd.province,sd.city,sd.district,u.id ,u.avatar ",
    })
    List<UserDto> findAllSales();

    @Select({
            "<script>",
            "SELECT o.user_record_id userRecordId ,o.tool_man_id id ",
            "FROM sd_order o WHERE ",
            " o.user_record_id is not null and o.tool_man_id is not null    ",
            " and o.order_time BETWEEN #{startMoth} and #{endMoth}  ",

            "GROUP BY o.user_record_id ,o.tool_man_id ",
            "</script>"
    })
    List<UserDto> findOrderNumberAndUID(@Param("startMoth") LocalDateTime startMoth,
                                        @Param("endMoth") LocalDateTime endMoth);

    @Select({
            "<script>",
            "SELECT count(o.id) totalOrder ,o.tool_man_id id ,o.user_record_id userRecordId ",
            "FROM sd_order o ",
            "where  o.order_time BETWEEN #{startMoth} and #{endMoth}  and  o.order_state in(2,3,12) and o.on_cancel  = 0 ",
            " and  o.user_record_id is not null and o.tool_man_id is not null  and  o.user_record_id  in ",
            " <foreach collection=\"ids\" item=\"ids\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{ids}",
            "</foreach>",
            "GROUP BY o.tool_man_id ,o.user_record_id ",
            "</script>",
    })
    List<UserDto> findOrderRateNumber(@Param("ids") List<Long> userRecordIds,
                                      @Param("startMoth") LocalDateTime startMoth,
                                      @Param("endMoth") LocalDateTime endMoth);


    @Select({
            "<script>",
            "SELECT count(o.id) totalOrder ,o.tool_man_id id  ",
            "FROM sd_order o ",
            "where  o.order_time BETWEEN #{startMoth} and #{endMoth}  ",
            " and  o.user_record_id is not null and o.tool_man_id is not null  and  o.tool_man_id  in ",
            " <foreach collection=\"ids\" item=\"ids\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{ids}",
            "</foreach>",
            "GROUP BY o.tool_man_id  ",
            "</script>",
    })
    List<UserDto> findOrderNumberNowMothAndUID(@Param("ids") List<Long> userId,
                                               @Param("startMoth") LocalDateTime startMoth,
                                               @Param("endMoth") LocalDateTime endMoth);

    @Select({
            "<script>",
            "SELECT IFNULL(count(sr.id),0) AS  customerWeek,",
            "u.id as id  ",
            "FROM sd_user_record sr, sys_user u  ",
            "WHERE sr.tool_man_id = u.id   and  sr.deleted_at IS NULL and sr.created_at >= #{startWeek}  ",
            " and  sr.tool_man_id  in ",
            " <foreach collection=\"ids\" item=\"ids\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{ids}",
            "</foreach>",
            "GROUP BY u.id ",
            "</script>"
    })
    List<UserDto> findUserRecordNumberWeek(@Param("ids") List<Long> userId,
                                           @Param("startWeek") LocalDateTime startWeek);

    @Select({
            "<script>",
            "SELECT IFNULL(count(sr.id),0) AS  customer,",
            "u.id as id  ",
            "FROM sd_user_record sr, sys_user u  ",
            "WHERE sr.tool_man_id = u.id   and  sr.deleted_at IS NULL   ",

            " and  sr.tool_man_id  in ",
            " <foreach collection=\"ids\" item=\"ids\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{ids}",
            "</foreach>",

            "GROUP BY u.id ",
            "</script>"
    })
    List<UserDto> findUserRecordNumber(@Param("ids") List<Long> userId);

    @Select({
            "<script>",
            "SELECT IFNULL(sum(o.amount),0) AS orderAmountWeek,",
            "o.tool_man_id as id ",

            "FROM sd_order o, sys_user su ",
            "WHERE o.tool_man_id=su.id  and o.order_state  in (2,3,12) and o.order_time >= #{startWeek} and o.on_cancel  = 0  ",
            " and  o.tool_man_id  in ",
            " <foreach collection=\"ids\" item=\"ids\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{ids}",
            "</foreach>",
            "GROUP BY o.tool_man_id ",
            "</script>"
    })
    List<UserDto> findUserOrderAmountWeek(@Param("ids") List<Long> userId,
                                          @Param("startWeek") LocalDateTime startWeek);


    @Select({
            "SELECT  st.name as name,st.id id",
            "FROM sys_dept st where st.enabled = 1",
            "GROUP BY st.name,st.id "
    })
    List<DeptDto> findDeptNAId();

    @Select({
            "<script>",
            "SELECT IFNULL(count(sr.id),0) AS  customer,",
            "u.dept_id as id   ",
            "FROM sd_user_record sr, sys_user u ,sd_tool_man_info sd ",
            "WHERE sr.tool_man_id = u.id   and sr.deleted_at IS NULL  ",
            " and u.id = sd.user_id and u.dept_id in ",
            " <foreach collection=\"ids\" item=\"userId\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{userId}",
            "</foreach>",

            "GROUP BY u.dept_id    ",
            "</script>"
    })
    List<DeptDto> findDeptNumbers(@Param("ids") List<Long> deptId);

    @Select({
            "<script>",
            "SELECT IFNULL(count(sr.id),0) AS  customerWeek,",
            "u.dept_id as id   ",
            "FROM sd_user_record sr, sys_user u ,sd_tool_man_info sd ",
            "WHERE sr.tool_man_id = u.id   and ( sr.deleted_at IS NULL and sr.created_at >= #{startWeek}) ",
            " and u.id = sd.user_id and u.dept_id in ",
            " <foreach collection=\"ids\" item=\"userId\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{userId}",
            "</foreach>",

            "GROUP BY u.dept_id    ",
            "</script>"
    })
    List<DeptDto> findDeptNumberWeek(@Param("ids") List<Long> deptId,
                                     @Param("startWeek") LocalDateTime startWeek);

    @Select({
            "<script>",
            "SELECT IFNULL(sum(o.amount),0) AS orderAmountWeek,",
            "su.dept_id as id ",

            "FROM sd_order o, sys_user su ,sd_tool_man_info sd ",
            "WHERE o.tool_man_id=su.id  and o.order_state in (2,3,12) and o.order_time >= #{startWeek} and sd.user_id = su.id and o.on_cancel  = 0  ",
            " and su.dept_id in ",
            " <foreach collection=\"ids\" item=\"deptId\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{deptId}",
            "</foreach>",

            "GROUP BY su.dept_id  ",
            "</script>"
    })
    List<DeptDto> findOrderAmountWeek(@Param("ids") List<Long> deptId,
                                      @Param("startWeek") LocalDateTime startWeek);

    @Select({
            "<script>",
            "  SELECT o.user_record_id userRecordId ,u.dept_id id ",
            " FROM sd_order o,sys_user u WHERE  ",
            " o.user_record_id is not null and o.tool_man_id is not null  ",
            " and o.order_time BETWEEN #{startMoth} and #{enMoth} and u.id = o.tool_man_id and u.dept_id in  ",
            " <foreach collection=\"ids\" item=\"ids\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{ids}",
            "</foreach>",
            " GROUP BY o.user_record_id ,u.dept_id ",
            "</script>",
    })
    List<DeptDto> findDeptOrderUserRecord(@Param("ids") List<Long> deptIds,
                                          @Param("startMoth") LocalDateTime startMoth,
                                          @Param("enMoth") LocalDateTime enMoth);

    @Select({
            "<script>",
            "  SELECT o.user_record_id userRecordId ,u.dept_id id , count(o.id) totalOrder ",
            " FROM sd_order o,sys_user u WHERE  ",
            " o.user_record_id is not null and o.tool_man_id is not null and  o.order_state in(2,3,12) and o.on_cancel  = 0 ",
            " and o.order_time BETWEEN #{startMoth} and #{enMoth} and u.id = o.tool_man_id and o.user_record_id in  ",
            " <foreach collection=\"ids\" item=\"ids\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{ids}",
            "</foreach>",
            " GROUP BY o.user_record_id ,u.dept_id ",
            "</script>",
    })
    List<DeptDto> findDeptOrderRateNumber(@Param("ids") List<Long> deptIds,
                                          @Param("startMoth") LocalDateTime startMoth,
                                          @Param("enMoth") LocalDateTime enMoth);

    @Select({
            "<script>",
            "select count(o.id) totalOrder,u.dept_id id from sd_order o,sys_user u where  ",
            "  u.id = o.tool_man_id  and o.order_time  BETWEEN #{startMoth} and #{enMoth} and u.dept_id in ",
            " <foreach collection=\"ids\" item=\"ids\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{ids}",
            "</foreach>",
            "GROUP BY u.dept_id  ",
            "</script>"
    })
    List<DeptDto> findOrderNumbers(@Param("ids") List<Long> deptIds,
                                   @Param("startMoth") LocalDateTime startMoth,
                                   @Param("enMoth") LocalDateTime enMoth);
}
